﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class BCNam_DAO
    {
        public static bool kiemTraBCNam(int nam)
        {
            string query = "select * from lichbay where year(ngaydi) = " + nam.ToString();
            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            if (dt.Rows.Count == 0)
                return false;
            return true;
        }
        public static List<BCNam_DTO> lapBaoCaoNam(int nam)
        {
            string query = "SELECT MONTH(LB2.NGAYDI) THANG, COUNT(LB2.MALB) SOCB, SUM(LB2.SOVEHANG1*CB.GIAVEHANG1 + LB2.SOVEHANG2*CB.GIAVEHANG2) DOANHTHU "
                            + " FROM CHUYENBAY CB JOIN (SELECT LB.MALB MALB, LB.MACB MACB, LB.NGAYDI NGAYDI,"
                            + " SUM (CASE WHEN VE.HANGVE =1 THEN 1 WHEN VE.HANGVE = 0 OR VE.HANGVE IS NULL THEN 0 END) SOVEHANG1, "
                            + " SUM (CASE WHEN VE.HANGVE =0 THEN 1 WHEN VE.HANGVE =1 OR VE.HANGVE IS NULL THEN 0 END) SOVEHANG2 "
                            + " FROM LICHBAY LB LEFT JOIN VE ON (LB.MALB = VE.MALB) "
                            + " WHERE (VE.LOAIVE =1 OR VE.LOAIVE IS NULL) AND YEAR(LB.NGAYDI) = " + nam.ToString()
                            + " GROUP BY LB.MALB, LB.MACB, LB.NGAYDI) LB2 ON (LB2.MACB = CB.MACB)"
                            + " GROUP BY MONTH(LB2.NGAYDI) ";
            
            DataTable bcnam_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (bcnam_tab == null)
                return null;
            List<BCNam_DTO> bcnam_list = new List<BCNam_DTO>();
            int tongDoanhThu = 0;
            for (int i = 0; i < bcnam_tab.Rows.Count; i++)
            {
                bcnam_list.Add(new BCNam_DTO());
                bcnam_list[i].thang = int.Parse(bcnam_tab.Select()[i]["Thang"].ToString());
                bcnam_list[i].soCB = int.Parse(bcnam_tab.Select()[i]["SoCB"].ToString());
                bcnam_list[i].doanhThu = int.Parse(bcnam_tab.Select()[i]["DoanhThu"].ToString());
                tongDoanhThu = tongDoanhThu + bcnam_list[i].doanhThu;
            }
            for (int i = 0; i < bcnam_list.Count; i++)
            {
                bcnam_list[i].tiLe = bcnam_list[i].doanhThu*100/tongDoanhThu;
            }

            return bcnam_list;
        }

        //thong ke tat ca cac nam hien co trong he thong
        public static List<int> thongKeNam()
        {
            string query = "select distinct year(ngaydi) nam from LichBay";
            DataTable nam_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (nam_tab == null)
                return null;
            List<int> nam_list = new List<int>();
            for (int i = 0; i < nam_tab.Rows.Count; i++)
            {
                nam_list.Add(int.Parse(nam_tab.Select()[i]["nam"].ToString()));
            }
            return nam_list;
        }
    }
}
